import datetime
import re
import json
import uuid
from io import BytesIO
from fastapi import APIRouter, File, Depends, Form, UploadFile
from fastapi.encoders import jsonable_encoder
from openpyxl.reader.excel import load_workbook
from sqlalchemy.orm import Session
from sqlalchemy.exc import IntegrityError
from models.db import get_db
from schemas import response_success, response_error
from schemas.cmdb import FieldCreateSchema, SchemaCreateSchema, SchemaUpdateSchema, ValueCreateSchema, \
    FieldUpdateSchema, instanceUpdateSchema, instanceImportSchema, instanceQuerySchema
from models import Field, Schema, Entity, Value
from sqlalchemy import and_, func, select

# from views.utils.types import Int

cmdb_router = router = APIRouter()


@router.post('/schema')
async def schema_create(form: SchemaCreateSchema, db: Session = Depends(get_db)):
    """创建资产类型"""
    schema = Schema(schema_id=form.schema_id, name=form.name, desc=form.desc)
    db.add(schema)
    db.flush()
    meta = {
        "type": "types.String",
        "hidden": False
    },

    db.add(Field(field_id='_c_time', name='创建时间', meta=json.dumps(meta), schema_id=schema.id,
                 desc='数据创建时间，系统自动创建, 请误删除'))
    db.add(Field(field_id='_u_time', name='更新时间', meta=json.dumps(meta), schema_id=schema.id,
                 desc='数据更新时间，系统自动创建，请误删除'))

    db.commit()
    return response_success("新增成功")


@router.put('/schema')
async def schema_update(form: SchemaUpdateSchema, db: Session = Depends(get_db)):
    """更新模型，及字段信息"""
    db_data = db.query(Schema).filter_by(id=form.id).first()
    db_data.name = form.name
    db_data.desc = form.desc

    delete_data = []
    if form.fields:
        """更新或新增字段"""
        add_fields = []
        update_fields = []
        for item in form.fields:
            if 'id' in item:
                update_fields.append(item)
            else:
                add_fields.append(item)
        update_field_ids = {x['id']: x for x in update_fields}

        field_names = {x.name: x for x in db_data.field}
        field_ids = {x.id: x for x in db_data.field}

        for item in update_fields:
            """更新字段"""

            if item['name'] in field_names:
                if item['id'] != field_names[item['name']].id:
                    return response_error(msg=f"已存在属性名称：{item['name']}")

            field = field_ids[item['id']]
            field.field_id = item['field_id']
            field.name = item['name']
            field.meta = item['meta']
            field.schema_id = db_data.id
            field.desc = item.get('desc')

        for item in add_fields:
            """添加字段"""
            field = Field(field_id=item['field_id'], name=item['name'], meta=item['meta'], schema_id=db_data.id,
                          desc=item.get('desc'))
            db.add(field)

        for item in db_data.field:
            """删除字段"""
            if item.id not in update_field_ids:
                delete_data.append(item)
    try:
        db.commit()
    except IntegrityError:
        return response_error(msg='类型名称重复')

    for item in delete_data:
        db.query(Field).filter_by(id=item.id).delete()

    for item in delete_data:
        """删除字段"""
        delete_data.append(item)

    db.commit()
    return response_success("更新成功")


@router.delete('/schema/{id}')
async def schema_delete(id: int, db: Session = Depends(get_db)):
    """创建资产类型"""
    db_data = db.query(Schema).filter_by(id=id).first()
    if not db_data:
        return response_error(msg='该数据不存在')

    db.query(Field).filter_by(schema_id=db_data.id).delete()
    db.delete(db_data)
    db.commit()
    return response_success("删除成功")


@router.post("/field")
async def field_create(schema: FieldCreateSchema, db: Session = Depends(get_db)):
    if schema.meta:
        schema.meta = json.dumps(schema.meta)
    field = Field(**jsonable_encoder(schema))
    db.add(field)
    db.flush()
    field.ref_id = field.id
    db.commit()
    return response_success("新增成功")


@router.get('/schema')
async def schema_list(db: Session = Depends(get_db)):
    values = []
    query = {}
    db_data = db.query(Schema).all()
    return response_success(data={'list': db_data, 'total': len(values)})


@router.get('/schema-detail/{schema_id}')
async def schema_list(schema_id: str, db: Session = Depends(get_db)):
    db_data = db.query(Schema).filter_by(schema_id=schema_id).first()
    return response_success(data={'detail': db_data})


@router.get('/field/{schema_id}')
async def field_list(schema_id: str, db: Session = Depends(get_db)):
    """查询字段列表"""
    db_fields = db.query(Field).filter_by(schema_id=schema_id).all()
    return response_success(data={'list': db_fields, 'total': len(db_fields)})


@router.post('/instance')
async def instance_create(form: ValueCreateSchema, db: Session = Depends(get_db)):
    db_schema = db.query(Schema).filter_by(schema_id=form.schema_id).first()

    # 新增实体数据
    entity = Entity(entity_id=uuid.uuid4().hex, schema_id=db_schema.id)
    db.add(entity)
    db.flush()

    for field in db_schema.field:
        if field.field_id not in form.data:
            continue

        value = Value(value=form.data[field.field_id], field_id=field.id, entity_id=entity.id)
        db.add(value)

    db.commit()
    return response_success("新增成功")


@router.put('/field')
async def field_update(form: FieldUpdateSchema, db: Session = Depends(get_db)):
    """更新字段"""
    db_field = db.query(Field).filter_by(id=form.id).first()

    for key, value in form.dict().items():
        if not value:
            continue
        setattr(db_field, key, value)
    db.commit()
    return response_success("字段更新成功")


from collections import defaultdict


@router.post('/instance-import-excel')
async def instance_import_excel(schema_id: str = Form(...), unique: str = Form(...), file: UploadFile = File(...),
                                db: Session = Depends(get_db)):
    """批量导入数据，自动处理新增及更新"""
    db_schema = db.query(Schema).filter_by(schema_id=schema_id).first()
    fields_names = {i.name: i for i in db_schema.field}
    fields_ids = {i.field_id: i for i in db_schema.field}

    contents = await file.read()
    wb = load_workbook(filename=BytesIO(contents))
    ws = wb.active

    # 获取 Excel 的标题行
    headers = [cell.value for cell in ws[1]]
    columns_to_keep = [(index, fields_names[header].field_id) for index, header in enumerate(headers) if
                       header in fields_names]

    # 构建保留的 Excel 数据，转换为字典格式
    values = [{header: row[index] for index, header in columns_to_keep} for row in
              ws.iter_rows(min_row=2, values_only=True)]

    # 查询现有的实体和值并缓存到字典
    db_entity = db.query(Entity).filter_by(schema_id=db_schema.id).all()
    entity_ids = [entity.id for entity in db_entity]
    field_id = fields_ids[unique].id

    db_values = db.query(Value).filter(Value.entity_id.in_(entity_ids), Value.field_id == field_id).all()
    unique_values = {db_value.value: db_value.entity_id for db_value in db_values}

    # 批量查询所有需要更新的字段值
    all_field_values = db.query(Value).filter(Value.entity_id.in_(entity_ids)).all()

    # 构建一个字典，方便快速查找字段值
    field_values_map = defaultdict(dict)
    for val in all_field_values:
        field_values_map[val.entity_id][val.field_id] = val

    new_entities = []
    new_values = []
    updated_values = []
    updated_new_values = []

    entity_max_id = db.query(func.max(Entity.id)).scalar()

    if entity_max_id:
        entity_max_id += 1
    else:
        entity_max_id = 1

    for index, row in enumerate(values):
        if row[unique] in unique_values:
            entity_id = unique_values[row[unique]]
            for k, v in row.items():
                if not v:
                    continue
                if entity_id in field_values_map and fields_ids[k].id in field_values_map[entity_id]:
                    db_value = field_values_map[entity_id][fields_ids[k].id]
                    db_value.value = v
                    updated_values.append(db_value)
                else:
                    updated_new_values.append(Value(value=v, field_id=fields_ids[k].id, entity_id=entity_id))
        else:
            present_id = entity_max_id + index
            # 新增实体和对应的值
            entity = Entity(id=present_id, entity_id=uuid.uuid4().hex, schema_id=db_schema.id)
            new_entities.append(entity)

            for k, v in row.items():
                if not v:
                    continue
                new_values.append(Value(value=v, field_id=fields_ids[k].id, entity_id=present_id))

    # 批量插入新增实体和对应的值
    if new_entities:
        db.add_all(new_entities)
        db.flush()

    if new_values:
        db.bulk_save_objects(new_values)
    if updated_new_values:
        db.bulk_save_objects(updated_new_values)
    if updated_values:
        db.bulk_update_mappings(Value, [v.__dict__ for v in updated_values])

    db.commit()
    return response_success('导入成功')


@router.post('/instance-import')
async def instance_import(form: instanceImportSchema, db: Session = Depends(get_db)):
    """批量导入数据，自动处理新增及更新"""
    db_schema = db.query(Schema).filter_by(schema_id=form.schema_id).first()
    fields_ids = {i.field_id: i for i in db_schema.field}

    db_entity = db.query(Entity).filter_by(schema_id=db_schema.id).all()

    entity_ids = [entity.id for entity in db_entity]
    # 查询所有需要的值
    db_values = db.query(Value).filter(Value.entity_id.in_(entity_ids),
                                       Value.field_id == fields_ids[form.unique].id).all()
    # 将查询结果转化为字典
    unique_values = {db_value.value: db_value.entity_id for db_value in db_values}
    for row in form.data:
        if row[form.unique] in unique_values:
            for k, v in row.items():
                db_value = db.query(Value).filter_by(entity_id=unique_values[row[form.unique]],
                                                     field_id=fields_ids[k].id).first()
                if db_value:
                    db_value.value = v
                else:
                    value = Value(value=v, field_id=fields_ids[k].id, entity_id=unique_values[row[form.unique]])
                    db.add(value)
            db_value = db.query(Value).filter_by(entity_id=unique_values[row[form.unique]],
                                                 field_id=fields_ids['_m_time'].id).first()
            if db_value:
                db_value.value = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            else:
                value = Value(value=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),
                              field_id=fields_ids['_m_time'].id, entity_id=unique_values[row[form.unique]])
                db.add(value)

        else:
            # 新增实体数据
            entity = Entity(entity_id=uuid.uuid4().hex, schema_id=db_schema.id)
            db.add(entity)
            db.flush()

            for k, v in row.items():
                if not v:
                    continue
                value = Value(value=v, field_id=fields_ids[k].id, entity_id=entity.id)
                db.add(value)
            value = Value(value=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'), field_id=fields_ids['_c_time'].id,
                          entity_id=entity.id)
            db.add(value)

    db.commit()
    return response_success('导入成功')


@router.post('/instance-list')
async def instance_list(form: instanceQuerySchema, db: Session = Depends(get_db)):
    """实例查询，包含全局模糊搜索，高级搜索"""
    if form.page < 0 or 1000 < form.rows < 5:
        return response_error('参数错误')

    db_schema = db.query(Schema).filter_by(schema_id=form.schema_id).first()
    if not db_schema:
        return response_error('模型ID不存在')

    db_field = db_schema.field
    field_ids = {x.field_id: x for x in db_field}
    db_entity_query = db.query(Entity).filter_by(schema_id=db_schema.id)

    if form.query:
        comparison_map = {
            'ilike': lambda val: Value.value.ilike(f"%{val}%"),
            'like': lambda val: Value.value.like(f"%{val}%"),
            '=': lambda val: func.lower(Value.value) == val.lower(),
            '>': lambda val: Value.value > val,
            '<': lambda val: Value.value < val,
            '>=': lambda val: Value.value >= val,
            '<=': lambda val: Value.value <= val
        }
        # 根据传入的条件动态构建查询
        query_cte = None
        for index, item in enumerate(form.query):
            field = item.get('field', '').strip()
            comparison = item.get('comparison', '').strip()
            if not (query_value := item.get('value', '').strip()) or not field:
                continue

            if field == '_full':
                query_entity_ids = db.query(Value.entity_id).filter(Value.value.ilike(f"%{query_value}%")).subquery()
            else:
                if field not in field_ids:
                    continue
                if bool(re.match(r'^-?\d+(\.\d+)?$', query_value)) and comparison != 'like':
                    query_value = eval(query_value)
                    filter_query = and_(Value.field_id == field_ids[field].id, Value.value == query_value)
                else:
                    if not (comparison_map_value := comparison_map.get(comparison)):
                        return ''
                    comparison_obj = comparison_map_value(query_value)
                    filter_query = and_(Value.field_id == field_ids[field].id, comparison_obj)
                query_entity_ids = db.query(Value.entity_id).filter(filter_query).subquery()

            # 第一次执行时，不使用 CTE
            if query_cte is None:
                # 初次构建查询
                query = db_entity_query.filter(Entity.id.in_(select(query_entity_ids)))
                query_cte = query.cte()  # 把初次结果作为 CTE

            else:
                # 继续使用上次的 CTE 作为基础查询
                query = db.query(query_cte).filter(
                    query_cte.c.id.in_(select(query_entity_ids))  # 在 CTE 的结果基础上过滤
                )
                query_cte = query.cte()  # 更新 CTE

            # 最后一次循环将 db_entity_query 设置为最终的查询结果
            if index == len(form.query) - 1:
                db_entity_query = query

    total = db_entity_query.count()

    # 对 Entity 表进行分页操作
    db_entitys = db_entity_query.offset(form.page * form.rows).limit(form.rows).all()
    # 获取分页后的 entity_ids
    entity_ids = [entity.id for entity in db_entitys]
    #
    # # 查询与分页后的 Entity 相关的 Value 表的数据
    db_value_query = db.query(Value).filter(Value.entity_id.in_(entity_ids))
    db_values = db_value_query.all()

    # 将 Values 数据按 entity_id 分组
    value_map = {}
    for value in db_values:
        if value.entity_id not in value_map:
            value_map[value.entity_id] = {}
        value_map[value.entity_id][value.field_id] = value

    data = []
    # 构建响应数据
    for entity in db_entitys:
        row = {}
        value_fields = value_map.get(entity.id, {})

        for field in db_field:
            if not field.meta.get('hidden'):
                value = value_fields.get(field.id)
                row[field.field_id] = value.value if value else ''
        row['instance_id'] = entity.entity_id
        data.append(row)

    return response_success(data={'list': data, 'field': db_field, 'total': total})


@router.get('/instance-detail/{instance_id}')
async def instance_detail(instance_id: str, db: Session = Depends(get_db)):
    """获取实例详情"""
    db_entity = db.query(Entity).filter_by(entity_id=instance_id).first()
    db_field = db.query(Field).filter_by(schema_id=db_entity.schema_id).all()
    db_value = db.query(Value).filter_by(entity_id=db_entity.id).all()

    value_fields = {x.field_id: x for x in db_value}
    data = {}
    for field in db_field:
        value = value_fields.get(field.id)
        if value:
            data[field.field_id] = value_fields[field.id].value
        else:
            data[field.field_id] = ''
        data['instance_id'] = db_entity.entity_id
    return response_success(data={'detail': data})


@router.put('/instance')
async def instance_update(form: instanceUpdateSchema, db: Session = Depends(get_db)):
    """更新实例"""
    db_entity = db.query(Entity).filter_by(entity_id=form.instance_id).first()
    db_fields = db.query(Field).filter_by(schema_id=db_entity.schema_id).all()
    db_values = db.query(Value).filter_by(entity_id=db_entity.id).all()
    values_field = {x.field_id: x for x in db_values}

    # 更新实体数据
    for field in db_fields:
        if field.field_id not in form.data:
            continue
        if field.id in values_field:
            values_field[field.id].value = form.data[field.field_id]
        else:
            value = Value(value=form.data[field.field_id], field_id=field.id, entity_id=db_entity.id)
            db.add(value)

    db.commit()
    return response_success("新增成功")


@router.delete('/instance/{instance_id}')
async def instance_update(instance_id: str, db: Session = Depends(get_db)):
    """删除实例"""
    db_entity = db.query(Entity).filter_by(entity_id=instance_id).first()
    db.query(Value).filter_by(entity_id=db_entity.id).delete()
    db.delete(db_entity)
    db.commit()
    return response_success("新增成功")


@router.post("/model-swift-create")
async def model_swift_create(form: str):
    db_data = await cmdb_model_collection.find_one({"id": form.model})
    if not db_data:
        return response_error(f'获取模型信息错误，{form.model}不存在！')
    swift = db_data.get('swift', [])
    swift_dict = {x['name']: x for x in swift}

    if form.name in swift_dict:
        return response_error('该查询条件已存在！')
    swift.append({
        'name': form.name,
        'query': form.query
    })

    result = await cmdb_model_collection.update_one(
        {"id": form.model},
        {"$set": {'swift': swift}}
    )

    if not result:
        return response_error('创建失败！')

    return response_success('创建成功')


@router.post("/model-swift-remove")
async def model_swift_remove(form: str):
    db_data = await cmdb_model_collection.find_one({"id": form.model})
    if not db_data:
        return response_error(f'获取模型信息错误，{form.model}不存在！')
    swift = db_data.get('swift', [])
    swift_dict = {x['name']: x for x in swift}

    if form.swift_name not in swift_dict:
        return response_error('该查询条件不存在！')
    swift = list(filter(lambda x: x['name'] != form.swift_name, swift))

    result = await cmdb_model_collection.update_one(
        {"id": form.model},
        {"$set": {'swift': swift}}
    )

    if not result:
        return response_error('删除失败！')

    return response_success('删除成功')


@router.get("/instance-history")
async def instance_history(page: int, page_size: int, model: str, ipmi_ip: str = None, attribute: str = None):
    data = []
    query = {'model': model}
    graph_query = {}

    if ipmi_ip:
        graph_query['ipmi_ip'] = ipmi_ip.strip()
    if attribute:
        query['attribute'] = attribute.strip()

    matcher = NodeMatcher(graph)
    instance_dict = {x.identity: x for x in matcher.match(model, **graph_query).all()}

    total = await instance_history_collection.count_documents(query)
    # async for i in instance_history_collection.find(query).skip((page - 1) * page_size).limit(page_size):
    async for i in instance_history_collection.find(query).skip((page - 1) * page_size).limit(page_size).sort('c_time',
                                                                                                              -1):
        i['_id'] = str(i['_id'])
        identity = int(i['instance_id'])
        if identity not in instance_dict:
            continue
        i['ipmi_ip'] = instance_dict[identity].get('ipmi_ip')
        data.append(i)

    return response_success(data={'list': jsonable_encoder(data), 'total': total})
